/*
 * Created on Jul 18, 2006
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package dbUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.Types;
import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;

/**
 * @author Shengru Tu
 * 
 *         TODO To change the template for this generated type comment go to
 *         Window - Preferences - Java - Code Style - Code Templates
 */
public class Populater {

	private Connection conn;

	public Populater(String host, int port, String dbName, String username,
			String passwd) throws SQLException {
		DBMySQL_Connection mySQL = new DBMySQL_Connection(host, port, dbName);
		conn = mySQL.getDBConnection(username, passwd);
	}

	/**
	 * This is a simple method insert rows into table Student by reading lines
	 * from a data file
	 */
	public int insertStudentRows(String dataFile) throws SQLException,
			IOException {
		// prepare an insert statement
		String stmt = "INSERT INTO student VALUES(?, ?, ?, ?, ?)";
		PreparedStatement pstmt = conn.prepareStatement(stmt);
		// open data file
		BufferedReader br = new BufferedReader(new FileReader(dataFile));
		int counter = 0;
		String line = br.readLine();
		while (line != null && line.trim().length() != 0) {
			String[] values = line.split("#");
			int id = Integer.parseInt(values[0]);
			pstmt.setInt(1, id);
			pstmt.setString(2, values[1]);
			pstmt.setString(3, values[2]);
			pstmt.setString(4, values[3]);
			pstmt.setString(5, values[4]);
			int n = pstmt.executeUpdate();
			counter = counter + n;
			line = br.readLine();
		}
		br.close();
		pstmt.close();
		return counter;
	}

	/**
	 * This is a generic method that can automatically detect the table schema.
	 * Fuctionanlity: Insert rows into a table by reading lines from a data file
	 */
	public int insertRows(String tableName, String dataFile)
			throws SQLException, IOException {
		// prepare an insert statement
		String tn = WebFilter.escapeQuotes(tableName);
		Statement sqlStmt = conn.createStatement();
		ResultSet rs = sqlStmt.executeQuery("SELECT * FROM " + tn);
		int[] colTypes = getColumnTypes(rs);
		sqlStmt.close();
		String stmt = formInsertPStmt(tn, colTypes.length);
		PreparedStatement pstmt = conn.prepareStatement(stmt);
		// open data file
		BufferedReader br = new BufferedReader(new FileReader(dataFile));
		int counter = 0;
		String line = br.readLine();
		// date formatter
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		while (line != null && line.trim().length() != 0) {
			String[] values = line.split("#");
			if (values.length < colTypes.length) {
				System.err.println("Data file's Line " + (counter+1) + " miessed data.");
				br.close();
				return -1;
			}
			for (int i = 0; i < colTypes.length; i++) {
				switch (colTypes[i]) {
				case Types.BOOLEAN: // 16
					boolean bool = (values[i] == "Y") ? true : false;
					pstmt.setBoolean(i + 1, bool);
					break;
				case Types.DATE: // 91
				case Types.TIMESTAMP: // 93
					java.util.Date dateval = null;
					try {
						dateval = sdf.parse(values[i]);
					} catch (ParseException pe) {
						System.err.println("Wrong date value in Line " + (counter + 1));
						pe.printStackTrace();
					}
					java.sql.Date date = new java.sql.Date(dateval.getTime());
					if (date != null)
						pstmt.setDate(i + 1, date);
					break;
				case Types.REAL: // 7
				case Types.DOUBLE: // 8
					double doubleval = Double.parseDouble(values[i]);
					pstmt.setDouble(i + 1, doubleval);
					break;
				case Types.FLOAT: // 6
					float floatval = Float.parseFloat(values[i]);
					pstmt.setFloat(i + 1, floatval);
					break;
				case Types.INTEGER: // 4
				case Types.NUMERIC: // 2
					int intval = Integer.parseInt(values[i]);
					pstmt.setInt(i + 1, intval);
					break;
				case Types.BIGINT: // -5
					long bigintval = Long.valueOf(values[i]);
					pstmt.setLong(i + 1, bigintval);
					break;
				case Types.VARCHAR: // 12
				case Types.LONGVARCHAR: // -1
				case Types.CHAR: // 1
					String strval = WebFilter.escapeXML(values[i]);
					pstmt.setString(i + 1, strval);
					break;
				default:
					pstmt.setString(i + 1, values[i]);
				}
			}
			counter = counter + pstmt.executeUpdate();
			line = br.readLine();
		}
		br.close();
		pstmt.close();
		return counter;
	}

	/**
	 * return the columns' types
	 */
	public int[] getColumnTypes(ResultSet rs) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		int col = rsmd.getColumnCount();
		int[] types = new int[col];
		for (int i = 0; i < col; i++) {
			types[i] = rsmd.getColumnType(i + 1);
		}
		return types;
	}

	/**
	 * return an INSERT statement with proper number of ?'s
	 */
	public String formInsertPStmt(String tableName, int colcnt)
			throws SQLException {
		String stmt = "INSERT INTO " + tableName + " VALUES(";
		for (int i = 0; i < colcnt; i++) {
			if (i > 0)
				stmt = stmt + ", ";
			stmt = stmt + "?";
		}
		stmt = stmt + ")";
		return stmt;
	}

	public static void main(String[] args) throws SQLException, IOException {
		if (args.length != 6 && args.length != 8) {
			System.out.println("usage: java Populater host port# db-name db-user db-pw studentlist any-table any-list");
			System.exit(1);
		}
		int portNum = Integer.parseInt(args[1]);
		Populater popu = new Populater(args[0], portNum, args[2], args[3],
				args[4]);
		int rowIns = popu.insertStudentRows(args[5]);
		System.out.println(rowIns + " rows were inserted into Table student.");
		if (args.length == 8) {
			rowIns = popu.insertRows(args[6], args[7]);
			System.out.println(rowIns + " rows were inserted into Table " + args[6] + ".");
		}

	}
}
